﻿using System;
using System.Data.SqlClient;
using AccessControl.Sida.Common;


namespace AccessControl.Sida.DataAccess
{
	public class StudentCardAccess
	{
		public SidaDS.StudentCardDataTable SelectStudentCard(String[] stcodeArray)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var filter = String.Join(",", stcodeArray);
			var cmdSelect =
					new SqlCommand(String.Format(
							"SELECT dbo.fsf.stcode, dbo.fsf.name, dbo.fsf.family, dbo.fsf.idd_meli, dbo.fresh.nameresh, CASE dbo.fsf.magh WHEN 1 THEN 'کارشناسي' WHEN 2 THEN 'کارداني' WHEN 3 THEN 'کارشناسي ناپيوسته' WHEN 4 THEN 'کارشناسي ارشد پيوسته' WHEN 5 THEN 'کارشناسي ارشد  ناپيوسته' WHEN 6 THEN 'دکتراي عمومي' WHEN 7 THEN 'دکتراي تخصصي' WHEN 8 THEN 'کارداني پيوسته' END AS magh, amozpic.dbo.stuimage.stu_pic FROM dbo.fsf LEFT JOIN amozpic.dbo.stuimage ON amozpic.dbo.stuimage.stcode COLLATE Arabic_CI_AS = dbo.fsf.stcode INNER JOIN dbo.fresh ON dbo.fsf.idresh = dbo.fresh.id WHERE dbo.fsf.stcode IN ({0})",filter), cn);
				
				var studentCardTable = new SidaDS.StudentCardDataTable();
				
				try
				{
					cn.Open();
					var reader = cmdSelect.ExecuteReader();
					if (reader != null)
						while (reader.Read())
						{
							var newRow = studentCardTable.NewStudentCardRow();
							newRow.stcode = (String) reader["stcode"];
							newRow.fullName = string.Concat((String)reader["name"], " ", (String)reader["family"]);
							newRow.idd_meli = (String) reader["idd_meli"];
							newRow.nameresh = (String) reader["nameresh"];
							newRow.magh = (String) reader["magh"];
							if (reader["stu_pic"]!=DBNull.Value)
							newRow.stu_pic = (Byte[]) reader["stu_pic"];
							studentCardTable.AddStudentCardRow(newRow);
						}
				}
				finally 
				{
					cn.Close();
				}
				return studentCardTable.Rows.Count != 0 ? studentCardTable : null; 
			}
		}
	}
}
